1 Imports System.Data.SqlClient
2 Public Class frmContacts
3
4 Private Sub btnClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClose.Click
5 Me.Close()
6 End Sub
7 Sub Reset()
8 txtContactPerson.Text = ""
9 txtContactNo.Text = ""
10 txtSearchByContactPerson.Text = ""
11 txtContactPerson.Focus()
12 Getdata()
13 btnSave.Enabled = True
14 btnDelete.Enabled = False
15 btnUpdate.Enabled = False
16 txtContactPerson.Focus()
17 End Sub
18 Private Sub btnNew_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNew.Click
19 Reset()
20 End Sub
21
22 Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
23 If txtContactPerson.Text = "" Then
24 MessageBox.Show("Please enter contact person", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
25 txtContactPerson.Focus()
26 Return
27 End If
28 If txtContactNo.Text = "" Then
29 MessageBox.Show("Please enter contact no.", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
30 txtContactNo.Focus()
31 Return
32 End If
33 Try
34 con = New SqlConnection(cs)
35 con.Open()
36 Dim ct As String = "select contactperson,contactno from Company_Contacts where ContactPerson=@d1 and ContactNo=@d2"
37 cmd = New SqlCommand(ct)
38 cmd.Parameters.AddWithValue("@d1", txtContactPerson.Text)
39 cmd.Parameters.AddWithValue("@d2", txtContactNo.Text)
40 cmd.Connection = con
41 rdr = cmd.ExecuteReader()
42
43 If rdr.Read() Then
44 MessageBox.Show("Record Already Exists", "Error", MessageBoxButtons.OK, MessageBoxIcon.[Error])
45 If (rdr IsNot Nothing) Then
46 rdr.Close()
47 End If
48 Return
49 End If
50
51 con = New SqlConnection(cs)
52 con.Open()
53
54 Dim cb As String = "insert into Company_Contacts(ContactPerson,ContactNo) VALUES (@d1,@d2)"
55 cmd = New SqlCommand(cb)
56 cmd.Parameters.AddWithValue("@d1", txtContactPerson.Text)
57 cmd.Parameters.AddWithValue("@d2", txtContactNo.Text)
58 cmd.Connection = con
59 cmd.ExecuteReader()
60 con.Close()
61 LogFunc(lblUser.Text, "added the new contact having contact name '" & txtContactPerson.Text & "' and Contact no. '" & txtContactNo.Text & "'")
62 MessageBox.Show("Successfully Saved", "Record", MessageBoxButtons.OK, MessageBoxIcon.Information)
63 btnSave.Enabled = False
64 Getdata()
65 Catch ex As Exception
66 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.[Error])
67 End Try
68 End Sub
69
70 Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
71 If txtContactPerson.Text = "" Then
72 MessageBox.Show("Please enter contact person", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
73 txtContactPerson.Focus()
74 Return
75 End If
76 If txtContactNo.Text = "" Then
77 MessageBox.Show("Please enter contact no.", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
78 txtContactNo.Focus()
79 Return
80 End If
81 Try
82 con = New SqlConnection(cs)
83 con.Open()
84 Dim cb As String = "Update Company_Contacts set ContactPerson=@d1,ContactNo=@d2 where ID=" & txtID.Text & ""
85 cmd = New SqlCommand(cb)
86 cmd.Parameters.AddWithValue("@d1", txtContactPerson.Text)
87 cmd.Parameters.AddWithValue("@d2", txtContactNo.Text)
88 cmd.Connection = con
89 cmd.ExecuteReader()
90 con.Close()
91 LogFunc(lblUser.Text, "updated the contact having contact name '" & txtContactPerson.Text & "' and Contact no. '" & txtContactNo.Text & "'")
92 MessageBox.Show("Successfully updated", "Record", MessageBoxButtons.OK, MessageBoxIcon.Information)
93 btnUpdate.Enabled = False
94 Getdata()
95 Catch ex As Exception
96 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.[Error])
97 End Try
98 End Sub
99 Private Sub DeleteRecord()
100
101 Try
102 Dim RowsAffected As Integer = 0
103 con = New SqlConnection(cs)
104 con.Open()
105 Dim cq As String = "delete from company_contacts where ID=@d1"
106 cmd = New SqlCommand(cq)
107 cmd.Parameters.AddWithValue("@d1", txtID.Text)
108 cmd.Connection = con
109 RowsAffected = cmd.ExecuteNonQuery()
110 If RowsAffected > 0 Then
111 LogFunc(lblUser.Text, "deleted the contact having contact name '" & txtContactPerson.Text & "' and Contact no. '" & txtContactNo.Text & "'")
112 MessageBox.Show("Successfully deleted", "Record", MessageBoxButtons.OK, MessageBoxIcon.Information)
113 Getdata()
114 Reset()
115 Else
116 MessageBox.Show("No Record found", "Sorry", MessageBoxButtons.OK, MessageBoxIcon.Information)
117 Reset()
118 End If
119 If con.State = ConnectionState.Open Then
120 con.Close()
121
122 End If
123 Catch ex As Exception
124 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.[Error])
125 End Try
126 End Sub
127 Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click
128 Try
129 If MessageBox.Show("Do you really want to delete this record?", "Confirmation", MessageBoxButtons.YesNo, MessageBoxIcon.Warning) = Windows.Forms.DialogResult.Yes Then
130 DeleteRecord()
131 End If
132 Catch ex As Exception
133 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
134 End Try
135 End Sub
136
137 Private Sub dgw_MouseClick(ByVal sender As Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles dgw.MouseClick
138 Try
139 If dgw.Rows.Count > 0 Then
140 Dim dr As DataGridViewRow = dgw.SelectedRows(0)
141 txtID.Text = dr.Cells(0).Value.ToString()
142 txtContactPerson.Text = dr.Cells(1).Value.ToString()
143 txtContactNo.Text = dr.Cells(2).Value.ToString()
144 btnUpdate.Enabled = True
145 btnDelete.Enabled = True
146 btnSave.Enabled = False
147 End If
148 Catch ex As Exception
149 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
150 End Try
151 End Sub
152
153 Private Sub dgw_RowPostPaint(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewRowPostPaintEventArgs) Handles dgw.RowPostPaint
154 Dim strRowNumber As String = (e.RowIndex + 1).ToString()
155 Dim size As SizeF = e.Graphics.MeasureString(strRowNumber, Me.Font)
156 If dgw.RowHeadersWidth < Convert.ToInt32((size.Width + 20)) Then
157 dgw.RowHeadersWidth = Convert.ToInt32((size.Width + 20))
158 End If
159 Dim b As Brush = SystemBrushes.ControlText
160 e.Graphics.DrawString(strRowNumber, Me.Font, b, e.RowBounds.Location.X + 15, e.RowBounds.Location.Y + ((e.RowBounds.Height - size.Height) / 2))
161
162 End Sub
163 Public Sub Getdata()
164 Try
165 con = New SqlConnection(cs)
166 con.Open()
167 cmd = New SqlCommand("SELECT RTRIM(ID),RTRIM(ContactPerson),RTRIM(ContactNo) from Company_Contacts order by ContactPerson", con)
168 rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
169 dgw.Rows.Clear()
170 While (rdr.Read() = True)
171 dgw.Rows.Add(rdr(0), rdr(1), rdr(2))
172 End While
173 con.Close()
174 Catch ex As Exception
175 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
176 End Try
177 End Sub
178
179
180 Private Sub frmcategory_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
181 Getdata()
182 End Sub
183
184 Private Sub txtSearchByContactPerson_TextChanged(sender As System.Object, e As System.EventArgs) Handles txtSearchByContactPerson.TextChanged
185 Try
186 con = New SqlConnection(cs)
187 con.Open()
188 cmd = New SqlCommand("SELECT RTRIM(ID),RTRIM(ContactPerson),RTRIM(ContactNo) from Company_Contacts where ContactPerson like '%" & txtSearchByContactPerson.Text & "%' order by ContactPerson", con)
189 rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
190 dgw.Rows.Clear()
191 While (rdr.Read() = True)
192 dgw.Rows.Add(rdr(0), rdr(1), rdr(2))
193 End While
194 con.Close()
195 Catch ex As Exception
196 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
197 End Try
198 End Sub
199 End Class